package com.logica.mdm.webfrontend.models.party;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import com.logica.mdm.webfrontend.utils.RequestWithParamsBean;

/**
 * Party search utilities.
 * @author Fabien Crespel <fabien.crespel@logica.com>
 */
public class PartySearchUtils {

	/**
	 * Create a RequestWithParamsBean from partySearchParams.
	 * @param partySearchParams
	 * @return SearchQueryRequest
	 */
	public static RequestWithParamsBean buildSearchQueryRequest(PartySearchParams partySearchParams) {
		// Build the search filter
		List<String> partyFilterCriteria = new ArrayList<String>();
		List<String> otherFilterCriteria = new ArrayList<String>();
		Map<String, Object> filterParams = new HashMap<String, Object>();
		
		if (!StringUtils.isEmpty(partySearchParams.getFirstName())) {
			partyFilterCriteria.add("LOWER(c_party.first_name) LIKE :firstName");
			filterParams.put("firstName", partySearchParams.getFirstName().toLowerCase() + "%");
		}
		if (!StringUtils.isEmpty(partySearchParams.getLastName())) {
			partyFilterCriteria.add("LOWER(c_party.last_name) LIKE :lastName");
			filterParams.put("lastName", partySearchParams.getLastName().toLowerCase() + "%");
		}
		if (partySearchParams.getBirthDate() != null) {
			otherFilterCriteria.add("c_person_details.birthdate = :birthDate");
			filterParams.put("birthDate", partySearchParams.getBirthDate());
		}
		if (!StringUtils.isEmpty(partySearchParams.getStreetNumber())) {
			otherFilterCriteria.add("LOWER(mv_pkg_ws_party_address_pp.street_number) LIKE :streetNumber");
			filterParams.put("streetNumber", "%" + partySearchParams.getStreetNumber().toLowerCase() + "%");
		}
		if (!StringUtils.isEmpty(partySearchParams.getStreetType())) {
			otherFilterCriteria.add("LOWER(mv_pkg_ws_party_address_pp.street_type) LIKE :streetType");
			filterParams.put("streetType", "%" + partySearchParams.getStreetType().toLowerCase() + "%");
		}
		if (!StringUtils.isEmpty(partySearchParams.getStreetName())) {
			otherFilterCriteria.add("LOWER(mv_pkg_ws_party_address_pp.street_name) LIKE :streetName");
			filterParams.put("streetName", "%" + partySearchParams.getStreetName().toLowerCase() + "%");
		}
		if (!StringUtils.isEmpty(partySearchParams.getPostalCode())) {
			otherFilterCriteria.add("mv_pkg_ws_party_address_pp.postal_cd LIKE :postalCode");
			filterParams.put("postalCode", "%" + partySearchParams.getPostalCode() + "%");
		}
		if (!StringUtils.isEmpty(partySearchParams.getCity())) {
			otherFilterCriteria.add("LOWER(mv_pkg_ws_party_address_pp.city_name) LIKE :cityName");
			filterParams.put("cityName", "%" + partySearchParams.getCity().toLowerCase() + "%");
		}
		if (!StringUtils.isEmpty(partySearchParams.getTelephoneNumber())) {
			otherFilterCriteria.add("(TRIM(mv_pkg_ws_party_phone.dom) = :homeNumber OR TRIM(mv_pkg_ws_party_phone.bur) = :workNumber OR TRIM(mv_pkg_ws_party_phone.por) = :cellNumber)");
			filterParams.put("homeNumber", partySearchParams.getTelephoneNumber().trim());
			filterParams.put("workNumber", partySearchParams.getTelephoneNumber().trim());
			filterParams.put("cellNumber", partySearchParams.getTelephoneNumber().trim());
		}
		if (!StringUtils.isEmpty(partySearchParams.getEmailAddress())) {
			otherFilterCriteria.add("LOWER(pkg_ws_party_e_address_pp.electronic_address) LIKE :electronicAddress");
			filterParams.put("electronicAddress", "%" + partySearchParams.getEmailAddress().toLowerCase() + "%");
		}
		
		// Create the SearchQuery request
		RequestWithParamsBean request = new RequestWithParamsBean();
		
		StringBuilder sb = new StringBuilder(4600);
		sb.append(PartySearchConstants.PARTY_FROM_QUERY);
		if (partyFilterCriteria.isEmpty()) {
			sb.append(PartySearchConstants.PARTY_C_PARTY_FROM);
		} else {
			sb.append(PartySearchConstants.PARTY_INNER_QUERY_1);
			sb.append(StringUtils.join(partyFilterCriteria.toArray(), " AND "));
			sb.append(PartySearchConstants.PARTY_INNER_QUERY_2);
		}
		sb.append(PartySearchConstants.PARTY_JOIN_QUERY);
		if (!otherFilterCriteria.isEmpty()) {
			sb.append(" WHERE ");
			sb.append(StringUtils.join(otherFilterCriteria.toArray(), " AND "));
		}
		sb.append(PartySearchConstants.PARTY_FINAL_FROM_NAME);
		
		request.setSearchQuery(sb.toString());
		request.setParams(filterParams);
		
		//setting the default order
		request.setSortCriteria("last_name ASC, first_name ASC");
		
		return request;
	}
	
	
	/**
	 * Build a SQL request for counting rows
	 * @param request the request to build
	 * @return the corresponding SQL request
	 */
	public static String buildCountQuery(RequestWithParamsBean request) {
		int sbSize = request.getSearchQuery().length() + PartySearchConstants.PARTY_COUNT_QUERY.length();
		StringBuilder sb = new StringBuilder(sbSize);
		sb.append(PartySearchConstants.PARTY_COUNT_QUERY);
		sb.append(request.getSearchQuery());
		return sb.toString();
	}
	
	/**
	 * Build a SQL request with pagination and Ordered field
	 * @param request the request to build
	 * @return the corresponding SQL request
	 */
	public static String buildPaginatedOrderedQuery(RequestWithParamsBean request) {
		int sbSize = request.getSearchQuery().length() + PartySearchConstants.PARTY_SELECT_QUERY.length() 
				+ PartySearchConstants.PARTY_START_PAGINATE.length() + PartySearchConstants.PARTY_END_PAGINATE.length()
				+ PartySearchConstants.PARTY_ORDER_BY.length();
		if (StringUtils.isNotBlank(request.getSortCriteria())) {
			sbSize += request.getSortCriteria().length();
		}
		
		StringBuilder sb = new StringBuilder(sbSize);
		sb.append(PartySearchConstants.PARTY_START_PAGINATE);
		sb.append(PartySearchConstants.PARTY_SELECT_QUERY);
		sb.append(request.getSearchQuery());
		if (StringUtils.isNotBlank(request.getSortCriteria())) {
			sb.append(PartySearchConstants.PARTY_ORDER_BY);
			sb.append(request.getSortCriteria());
		}
		sb.append(PartySearchConstants.PARTY_END_PAGINATE);
		return sb.toString();
	}

}
